package zjs.dc.controller;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

/**
 * @Class:
 * @Author: xyl
 * @Description: TODO
 * @Date: 2023/11/17 16:30
 */
public class ZjsTFinalCase {
    public static void main(String[] args) {
        SparkSession spark = SparkSession.builder().appName("zjs_t_final_case").enableHiveSupport().getOrCreate();
        spark.sparkContext().setLogLevel("ERROR");
        Dataset<Row> zs_data = spark.sql("select * from a_dc_ep_ods.zs_t_final_case");
        long zs_count = zs_data.count();
        if(zs_count>0){
            //修改
            spark.sql("insert overwrite table a_dc_ep_incr.zjs_t_final_case select " +
                    "md5(concat_ws('',translate(translate(zs.casecode,\"（\",\"(\"),\"）\",\")\"),translate(translate(zs.iname,\"（\",\"(\"),\"）\",\")\"))) as id," +
                    "translate(translate(zs.casecode,\"（\",\"(\"),\"）\",\")\") as case_no," +
                    "MD5(CONCAT('ExportFromMongo_',qcc.company_id)) as company_id," +
                    "translate(translate(zs.iname,\"（\",\"(\"),\"）\",\")\") as iname," +
                    "if(zs.creditcode is null or zs.creditcode='',zs.cardnum,zs.creditcode) as cardnum," +
                    "zs.courtname," +
                    "zs.regdate as regdate," +
                    "zs.finaldate as finaldate," +
                    "zs.execmoney as execmoney," +
                    "zs.unperfmoney as unperfmoney," +
                    "'1' as remark," +
                    "zs.jobid as dates," +
                    "'0' as isadd " +
                    "from a_dc_ep_ods.zs_t_final_case zs " +
                    "inner join (select * from a_dc_ep_ods.t_eci_company where credit_code is not null and credit_code!='') qcc on upper(trim(zs.creditcode)) = upper(trim(qcc.credit_code))");

            //删除
            spark.sql("insert into table a_dc_ep_incr.zjs_t_final_case " +
                    "select t1.id,t1.case_no,t1.company_id,t1.iname,t1.cardnum,t1.courtname,t1.regdate,t1.finaldate,t1.execmoney,t1.unperfmoney,t1.remark,t2.jobid,'-1' " +
                    "from a_dc_ep_dwi.zjs_t_final_case t1 " +
                    "inner join (select distinct casecode,jobid from a_dc_ep_ods.zs_t_final_case_del )t2 " +
                    "on translate(translate(t1.case_no,\"（\",\"(\"),\"）\",\")\") = translate(translate(t2.casecode,\"（\",\"(\"),\"）\",\")\")");
        }else {
            spark.sql("TRUNCATE TABLE a_dc_ep_incr.zjs_t_final_case");
            System.out.println("中数本期数据涉及该表的数据为空。。。。。。");
        }
        spark.stop();
    }
}
